/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nc.kyiv.yellow.sociallibrary.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author My
 */
public class MyDAO {
    DataSource source = null;

    public MyDAO() throws Exception {
        if (source != null) {
            return;
        }
        try {
            Context ic = new InitialContext();
            source = (DataSource) ic.lookup("jdbc/sociallibraryConnection");
        } catch (NamingException ex) {
            throw ex;
        }
        if (source == null) {
            throw new Exception("Lookup failed");
        }
    }
    
    public Connection getConnection() throws SQLException {
        try {
            Connection conn = source.getConnection();
            conn.setAutoCommit(false);
            return conn;
        } catch (SQLException ex) {
            throw ex;
        }
    }
    
    public void releaseConnection(Connection conn) throws SQLException {
        try {
            conn.close();
        } catch (SQLException ex) {
            throw ex;
        }
    }
    
        public LibUser getUserByLogin(Map params) throws SQLException, Exception {
        Connection conn = null;
        LibUser user = null;
        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT login, first_name, pass FROM lib_user WHERE login = '" 
                    + params.get("login") + "' AND pass = '" + params.get("pass") + "'");

            if (rs.isBeforeFirst()) {
                rs.next();
                user = new LibUser(rs.getString("login"), rs.getString("first_name"), rs.getString("pass"));

            } else {
                user = null;
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
//        if (user != null){
//            throw new Exception("Error in DAO");
//        }
        return user;
    }

    public void registerUser(Map params) throws SQLException {
        Connection conn = null;
        String query = "INSERT INTO lib_user (role, login, last_name, first_name, pass, email, status_id, reg_date) "
                + "values("
                + "(select id from role where role = 'beginner'),"
                + "'" + params.get("login") + "',"
                + "'" + params.get("last_name") + "',"
                + "'" + params.get("first_name") + "',"
                + "'" + params.get("pass") + "',"
                + "'" + params.get("email") + "',"
                + "(select id from user_status where status = 'active'),"
                + "TO_DATE(sysdate, 'DD-MM-YYYY'))";

        try {
            conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.execute(query);

        } catch (SQLException ex) {
            throw ex;
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
    }

    public void addBook(Map params) throws SQLException {
        Connection conn = null;
        conn = getConnection();
        
        String query = "INSERT INTO book (isbn, title, description, page_number, regist_date, moder_status) "
                + "values("
                + "'" + params.get("isbn") + "',"
                + "'" + params.get("title") + "',"
                + "'" + params.get("description") + "',"
                + "'" + params.get("page_number") + "',"
                + "TO_DATE(sysdate, 'DD-MM-YYYY'),"
                + "(select id from moder_status where status = 'new'))";
        
        try {
            Statement stmt = conn.createStatement();
            stmt.execute(query);

        } catch (SQLException ex) {
            throw ex;
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
    }
    
}
